from App.ext import db
from flask import Blueprint


# 用户账号
class Account(db.Model):
    # 定义表名
    __tablename__ = 'account'
    # 定义字段
    userid = db.Column(db.String(20), primary_key=True)  # 账号
    password = db.Column(db.String(20))  # 密码

    students = db.relationship('Student', backref='account')

    def keys(self):
        return ['userid', 'password']

    def __getitem__(self, item):
        return self.__getattribute__(item)

    def __repr__(self):
        return '<Account : %s %s>' % (self.userid,self.password)

    def tojson(self):
        return {
            'userid' : self.userid,
            'password' : self.password
        }

def getAllAccount():
    return Account.query.all()

def getAccountByUsernameAndPassword(account):
    return db.session.query(Account).filter(Account.userid == account.userid,Account.password == account.password).first()

def getAccountByUsername(username):
    return db.session.query(Account).filter(Account.userid == username).first()

def addAccount(account):
    db.session.add(account)
    db.session.commit()

def changePassword(account):
    a = db.session.query(Account).filter(Account.userid == account.userid).first()
    a.password = account.password
    db.session.commit()

def deleteAccountByUserid(userid):
    a = db.session.query(Account).filter(Account.userid == userid).first()
    db.session.delete(a)
    db.session.commit()

#学生
class Student(db.Model):
    __tablename__ = 'student'
    sid = db.Column(db.String(20), primary_key=True)  # 学号
    sname = db.Column(db.String(20))  # 学生姓名
    classid = db.Column(db.String(20))  # 学生所属班级
    userid = db.Column(db.String(20), db.ForeignKey('account.userid'))  # 学生的账号 外键  表名.字段
    email = db.Column(db.String(255))  # 邮箱

    def keys(self):
        return ['sid','sname','classid','userid','email']

    def __getitem__(self, item):
        return self.__getattribute__(item)

    def __repr__(self):
        return '<Student : %s %s %s %s %s>' % (self.sid,self.sname,self.classid,self.userid,self.email)

def getAllStudent():
    return Student.query.all()

def getStudentByUserid(userid):
    return db.session.query(Student).filter(Student.userid == userid).first()

def getStudentByClass(classid):
    return db.session.query(Student).filter(Student.classid == classid).first()

def addStudent(student):
    db.session.add(student)
    db.session.commit()

def updateStudent(student):
    s = db.session.query(Student).filter(Student.sid == student.sid).first()
    s.sname = student.sname
    s.classid = student.classid
    s.email = student.email
    db.session.commit()

def deleteStudent(sid):
    s = db.session.query(Student).filter(Student.sid == sid).first()
    db.session.delete(s)
    db.session.commit()

#宿舍
class Dorm(db.Model):
    __tablename__ = 'dorm'
    did = db.Column(db.String(20),primary_key=True)#宿舍号
    snum = db.Column(db.Integer) #宿舍内入住的学生数量
    building = db.Column(db.String(20))#楼
    waterfee = db.Column(db.Integer)#剩余水费
    electfee = db.Column(db.Integer)#剩余电费
    water = db.Column(db.Integer)  # 用水量
    electricity = db.Column(db.Integer)  # 用电量
    temperature = db.Column(db.Integer)#当前温度
    humidity = db.Column(db.Integer)#当前湿度
    door = db.Column(db.Boolean)#门的开闭 false为闭
    monitor = db.Column(db.String(255))#监控信息 暂时不知道应该怎么设计
    master = db.Column(db.String(20),db.ForeignKey('student.sid'))#寝室长

    def keys(self):
        return ['did','snum','building','waterfee','electfee','water','electricity','temperature','humidity','door','monitor','master']

    def __getitem__(self, item):
        return self.__getattribute__(item)

    def __repr__(self):
        return '<Dorm : %s %s %s %s %s %s %s %s %s %s>' % (self.did,self.snum,self.waterfee,self.electfee,self.water,self.electricity,self.temperature,self.humidity,self.door,self.monitor)

def getAllDorm():
    return Dorm.query.all()

def getDormById(did):
    return db.session.query(Dorm).filter(Dorm.did == did).first()

def addDorm(dorm):
    db.session.add(dorm)
    db.session.commit()

def updateDorm(dorm):
    d = db.session.query(Dorm).filter(Dorm.did == dorm.did).first()
    d.snum = dorm.snum
    d.waterfee = dorm.waterfee
    d.electfee = dorm.electfee
    d.water = dorm.water
    d.electricity = dorm.electricity
    d.temperature = dorm.temperature
    d.humidity = dorm.humidity
    d.door = dorm.door
    d.monitor = dorm.monitor
    d.master = dorm.master
    db.session.commit()

def deleteDorm(did):
    d = db.session.query(Dorm).filter(Dorm.did == did).first()
    db.session.delete(d)
    db.session.commit()

#住宿关系
class Accommodation(db.Model):
    __tablename__ = 'accommodation'
    sid = db.Column(db.String(20),db.ForeignKey('student.sid'),primary_key=True)#学号
    did = db.Column(db.String(20),db.ForeignKey('dorm.did'),primary_key=True)#宿舍号
    bedid = db.Column(db.String(20))#床号

    def keys(self):
        return ['sid','did','bedid']

    def __getitem__(self, item):
        return self.__getattribute__(item)

    def __repr__(self):
        return '<Accommodation : %s %s %s>' % (self.sid,self.did,self.bedid)

def getAllAccommodation():
    return Accommodation.query.all()

def getAccommodationBySid(sid):
    return db.session.query(Accommodation).filter(Accommodation.sid == sid).all()

def getAccommodationByDid(did):
    return db.session.query(Accommodation).filter(Accommodation.did == did).all()

def getAccommodationBySidAndDid(sid,did):
    return db.session.query(Accommodation).filter(Accommodation.sid == sid,Accommodation.did == did).first()

def addAccommodation(accommodation):
    db.session.add(accommodation)
    db.session.commit()

def updateAccommodationBySid(accommodation):
    acc = db.session.query(Accommodation).filter(Accommodation.sid == accommodation.sid).first()
    acc.did = accommodation.did
    acc.bedid = accommodation.bedid
    db.session.commit()

def updateAccommodationByDid(accommodation):
    acc = db.session.query(Accommodation).filter(Accommodation.did == accommodation.did).first()
    acc.sid = accommodation.sid
    acc.bedid = accommodation.bedid
    db.session.commit()

def deleteAccommodationBySid(sid):
    acc = db.session.query(Accommodation).filter(Accommodation.sid == sid).first()
    db.session.delete(acc)
    db.session.commit()

def deleteAccommodationByDid(did):
    db.session.query(Accommodation).filter(Accommodation.did == did).delete()#待测试
    db.session.commit()

#水费缴费记录
class Wayer_pay(db.Model):
    __tablename__ = 'water_pay'
    payid = db.Column(db.Integer,autoincrement=True,primary_key=True)#缴费单号
    sid = db.Column(db.String(20),db.ForeignKey('student.sid'))#宿舍号
    amount = db.Column(db.Integer)#金额
    #date = db.Column(db.DateTime(timezone=True), server_default=func.now())#缴费日期
    date = db.Column(db.TIMESTAMP)

    def keys(self):
        return ['payid','sid','amount','date']

    def __getitem__(self, item):
        return self.__getattribute__(item)

    def __repr__(self):
        return '<Water_pay : %s %s %s %s>' % (self.payid,self.sid,self.amount,self.date)

def getWaterpayByDid(sid):
    return db.session.query(Wayer_pay).filter(Wayer_pay.sid == sid).all()

def addWaterpay(waterpay):
    db.session.add(waterpay)
    db.session.commit()

#电费缴费记录
class Electricity_pay(db.Model):
    __tablename__ = 'electricity_pay'
    payid = db.Column(db.Integer,autoincrement=True,primary_key=True)#缴费单号
    sid = db.Column(db.String(20), db.ForeignKey('student.did'))  # 宿舍号
    amount = db.Column(db.Integer)  # 金额
    #date = db.Column(db.DateTime(timezone=True), server_default=func.now())  # 缴费日期
    date = db.Column(db.TIMESTAMP)

    def keys(self):
        return ['payid', 'sid', 'amount', 'date']

    def __getitem__(self, item):
        return self.__getattribute__(item)

    def __repr__(self):
        return '<Electricity_pay : %s %s %s %s>' % (self.payid,self.sid,self.amount,self.date)

def getElectricitypayByDid(sid):
    return db.session.query(Electricity_pay).filter(Electricity_pay.sid == sid).all()

def addElectricitypay(electricitypay):
    db.session.add(electricitypay)
    db.session.commit()

#闯入记录
class Break(db.Model):
    __tablename__ = 'break'
    breakid = db.Column(db.Integer,autoincrement=True,primary_key=True)#闯入记录id
    did = db.Column(db.String(20), db.ForeignKey('dorm.did'))  # 宿舍号
    breaker = db.Column(db.String(255))#闯入者信息 通过人脸识别
    entertime = db.Column(db.TIMESTAMP)#闯入时间
    leavetime = db.Column(db.TIMESTAMP)#离开时间
    level = db.Column(db.Integer)#报警等级

    def keys(self):
        return ['breakid', 'did', 'breaker', 'entertime','leavetime','level']

    def __getitem__(self, item):
        return self.__getattribute__(item)

    def __repr__(self):
        return '<Break : %s %s %s %s %s>' % (self.breakid,self.did,self.breaker,self.entertime,self.leavetime)

def getBreakByDid(did):
    return db.session.query(Break).filter(Break.did == did).all()

def addBreak(breakinfo):
    db.session.add(breakinfo)
    db.session.commit()

#报警记录
class Alarm(db.Model):
    __tablename__ = 'alarm'
    alarmid = db.Column(db.Integer,autoincrement=True,primary_key=True)#报警记录id
    did = db.Column(db.String(20), db.ForeignKey('dorm.did'))  # 宿舍号
    massage = db.Column(db.String(20))#报警信息
    #date = db.Column(db.DateTime(timezone=True), server_default=func.now())#报警时间
    date = db.Column(db.TIMESTAMP)
    level = db.Column(db.Integer)
    def keys(self):
        return ['alarmid', 'did', 'massage', 'date','level']

    def __getitem__(self, item):
        return self.__getattribute__(item)
    def __repr__(self):
        return '<Alarm : %s %s %s %s>' % (self.alarmid,self.did,self.massage,self.date)

def getAlarmByDid(did):
    return db.session.query(Alarm).filter(Alarm.did == did).all()

def addAlarm(alarm):
    db.session.add(alarm)
    db.session.commit()